//
//  ViewController.swift
//  FMDB
//
//  Created by 雷路荣 on 16/1/3.
//  Copyright © 2016年 leilurong. All rights reserved.
//

import UIKit

class ViewController: UIViewController {

    override func viewDidLoad() {
        super.viewDidLoad()
        //testInsert()
        //testUpdate()
        //testDelete()
        //testManyInsert()
        testQuery()
    }
    
    ///查询数据
    func testQuery() {
        let sql = "SELECT * FROM \(T_Person) LIMIT 10;"
        SQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            
            if let resultSet = db.executeQuery(sql) {
                print("查询到了数据")
                //获取下一条数据
                while resultSet.next() {
                    //获取列数
                    //resultSet.columnCount()
                    //通过字段名称来获取值
                    let id = resultSet.intForColumn("id")
                    //通过字段的顺序
                    let idx = resultSet.intForColumnIndex(0)
                    print("id:\(id), idx:\(idx)")
                    let name = resultSet.stringForColumn("name")
                    let age = resultSet.intForColumn("age")
                    let height = resultSet.doubleForColumn("height")
                    print("id: \(id), name: \(name), age: \(age), height: \(height)")
                    print("-------")
                }
            }else{
                print("没有查询到数据")
            }
        }
    }
    
    ///批量添加数据
    func testManyInsert() {
        print("批量添加数据开始")
        let sql = "INSERT INTO \(T_Person) (name, age, height) VALUES ('a', 20, 1.78);"
        let beginTime = CFAbsoluteTimeGetCurrent()
        // 执行一个事务, rollback: 表示是否回滚
        SQLiteManager.sharedManager.dbQueue.inTransaction { (db, rollback) -> Void in
            for i in 0..<10000 {
                db.executeUpdate(sql)
                if i == 1000 {
                    // 操作失败,回滚事务
                    // true表示回滚
                    rollback.memory = true
                    print("回滚事务")
                    break
                }
            }
        }
        let endTime = CFAbsoluteTimeGetCurrent()
        print("消耗时间:\(endTime - beginTime)")
    }
    ///测试添加数据
    func testInsert() {
        // 1. sql   ?表示占位.在执行的时候在来绑定数据
        let sql = "INSERT INTO \(T_Person) (name, age, height) VALUES (?, ?, ?);"
        //2 执行sql
        SQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql, "LMJ", 23, 1.66) {
            let id = db.lastInsertRowId()
                print("执行添加数据库成功，id:\(id)")
            }else{
                print("执行添加数据库失败")
            }
        }
    }
    
    ///更新数据
    func testUpdate() {
        let sql = "UPDATE \(T_Person) SET name = ?, age = ?, height = ? WHERE id = ?;"
        //执行
        SQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql, "wangwu", 22, 1.68, 2) {
                print("数据更新成功")
            }else{
                print("数据更新失败")
            }
        }
    }
    
    ///删除数据
    func testDelete() {
        let sql = "DELETE FROM \(T_Person) WHERE id = 1;"
        SQLiteManager.sharedManager.dbQueue.inDatabase { (db) -> Void in
            if db.executeUpdate(sql) {
                print("删除成功")
            }else{
                print("删除失败")
            }
        }
    }
}

